package it.uniroma2.db;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class Dbcontroller {
	
	private Statement stmt;
	private final String dbName = new String("questiondb");
	private final String dbHost = new String("127.0.0.1");
	private final String dbPort = new String("3306");
	private final String dbUser = new String("root");
	private final String dbPass = new String("");
	
	public boolean queryDb(String query){
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			//String url = "jdbc:mysql://127.0.0.1:3306/questiondb"; 
			String url ="jdbc:mysql://" + dbHost + ":" + dbPort + "/" + dbName;
			 Connection con = DriverManager.getConnection(url,dbUser, dbPass);
			 stmt = con.createStatement();
			 stmt.execute(query);
			 stmt.close();
			 con.close();
			
		} catch (ClassNotFoundException e) {
			System.out.println("Errore nella connessione al DB 1");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("Errore nella connessione al DB 2");
			e.printStackTrace();
		} 
		return true;
		
	}
	
	public boolean insertBlob(String query, String urlBlob){
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
		
			String url ="jdbc:mysql://" + dbHost + ":" + dbPort + "/" + dbName;
			 Connection con = DriverManager.getConnection(url,dbUser, dbPass);
			 con.setAutoCommit(false);
			 
			 PreparedStatement ps = con.prepareStatement(query);
			 
			 File blob = new File(urlBlob);
			 
			 FileInputStream fis = new FileInputStream(blob);
			 ps.setBinaryStream( 1, fis, (int) blob.length()  );
			 ps.execute();
			 con.commit();
			 fis.close();
			 
			 con.close();
			
		} catch (ClassNotFoundException e) {
			System.out.println("Errore nella connessione al DB 1");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("Errore nella connessione al DB 2");
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			System.out.println("Errore lettura file");
			e.printStackTrace();
		} catch (IOException e) {
			System.out.println("Errore chiusura file");
			e.printStackTrace();
		} 
		return true;
		
	}
	
	//Metodo che verifica l'esistenza di un'istanza nel DB
	public boolean verifyInstance(String inst){
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url ="jdbc:mysql://" + dbHost + ":" + dbPort + "/" + dbName;
			 Connection con = DriverManager.getConnection(url,dbUser, dbPass);
			 stmt = con.createStatement();
			 ResultSet rs;
			 
			 rs = stmt.executeQuery("SELECT ontology FROM mr_ontindex");
			 while ( rs.next() ) {
	                String onto = rs.getString("ontology");
	                if(onto.compareTo(inst) == 0){
	                	 stmt.close();
	        			 con.close();
	                	return true;
	                }
	            }
			 stmt.close();
			 con.close();
			
		} catch (ClassNotFoundException e) {
			System.out.println("Errore nella connessione al DB 1");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("Errore nella connessione al DB 2");
			e.printStackTrace();
		} 
		
		
		return false;
	}
	
	
	
	
}
